package com.web.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.web.dao.UserDAO;
import com.web.model.User;
import com.web.util.DBConnection;

public class UserDAOImp implements UserDAO {

	private static final String FIND_ALL_USER = "select * from d_user";
	private static final String INSERT = "insert into d_user(email,nickname,password,user_integral,is_email_verify,email_verify_code,last_login_time,last_login_ip) values(?,?,?,?,?,?,?,?)";
	private static final String FINDBYEMAIL = "select * from d_user where email=?";
	private static final String FINDBYID = "select * from d_user where id=?";
	private static final String UPDATE = "update d_user set email=?,nickname=?,password=?,user_integral=?,is_email_verify=?,email_verify_code=?,last_login_time=?,last_login_ip=? where id=?";
	
	//用户列表查询
	@SuppressWarnings("finally")
	@Override
	public List<User> findAll() throws Exception {

		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		List<User> resultList = null;
		try{
			conn=DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_ALL_USER);
			rs=prep.executeQuery();
			resultList = new ArrayList<User>();
			User user = null;
			while(rs.next()){
				user = new User();
				user.setId(rs.getInt("id"));
				user.setEmail(rs.getString("email"));
				user.setNickname(rs.getString("nickname"));
				user.setPassword(rs.getString("password"));
				user.setUserIntegral(rs.getInt("user_integral"));
				user.setEmailVerify(rs.getBoolean("is_email_verify"));
				user.setEmailVerifyCode(rs.getString("email_verify_code"));
				user.setLastLoginTime(rs.getLong("last_login_time"));
				user.setLastLoginIp(rs.getString("last_login_ip"));
				resultList.add(user);	
			}
		}catch(Exception e ){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs,prep,conn);
			return resultList;
		}
		
	}

	//保存用户
	@Override
	public void save(User user) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		try{
			conn= DBConnection.getConnection();
			prep= conn.prepareStatement(INSERT,Statement.RETURN_GENERATED_KEYS);
			prep.setString(1, user.getEmail());
			prep.setString(2, user.getNickname());
			prep.setString(3, user.getPassword());
			prep.setInt(4, user.getUserIntegral());
			if(user.isEmailVerify()) {
				prep.setString(5, "T");
				} else{
					prep.setString(5, "F");
					}
			prep.setString(6, user.getEmailVerifyCode());
			prep.setLong(7, user.getLastLoginTime());
			prep.setString(8, user.getLastLoginIp());
			prep.executeUpdate();
			ResultSet rst= prep.getGeneratedKeys();
			rst.next();
			int id= rst.getInt(1);
			user.setId(id);
			} finally{
				DBConnection.close(null, prep, conn);
				}
	}

	//根据email查用户
	
	@Override
	public User findByEmail(String email) throws Exception {
		PreparedStatement prep=null;
		Connection conn=null;
		User u=null;
		ResultSet rst=null;
		try{
			conn=DBConnection.getConnection();
			prep=conn.prepareStatement(FINDBYEMAIL);
			prep.setString(1, email);
			rst=prep.executeQuery();
			if(rst.next()){
				u=new User();
				u.setEmail(rst.getString("email"));
				u.setEmailVerifyCode(rst.getString("email_verify_code"));
				u.setId(rst.getInt("id"));
				u.setLastLoginIp(rst.getString("last_login_ip"));
				u.setLastLoginTime(rst.getLong("last_login_time"));
				u.setNickname(rst.getString("nickname"));
				u.setPassword(rst.getString("password"));
				u.setUserIntegral(rst.getInt("user_integral"));
				String is_email_verify= rst.getString("is_email_verify");
				if(is_email_verify.equals("T")){
					u.setEmailVerify(true);
				}else{
					u.setEmailVerify(false);
				}
			  }
		}finally{
			DBConnection.close(rst, prep, conn);
		}
		return u;
	}

	@Override
	public User findById(int id) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		User u= null;
		try{
			conn= DBConnection.getConnection();
			prep= conn.prepareStatement(FINDBYID);
			prep.setInt(1, id);
			ResultSet rst= prep.executeQuery();
			if(rst.next()) {
				u= new User();
				u.setEmail(rst.getString("email"));
				u.setEmailVerifyCode(rst.getString("email_verify_code"));
				u.setId(rst.getInt("id"));
				u.setLastLoginIp(rst.getString("last_login_ip"));
				u.setLastLoginTime(rst.getLong("last_login_time"));
				u.setNickname(rst.getString("nickname"));
				u.setPassword(rst.getString("password"));
				u.setUserIntegral(rst.getInt("user_integral"));
				String is_email_verify= rst.getString("is_email_verify");
				if(is_email_verify.equals("T")) {
					u.setEmailVerify(true);
		        } else{
		        	u.setEmailVerify(false);
		          }
			
			}
		} finally{
			DBConnection.close(null, prep, conn);
			}return u;
	}

	@Override
	public void update(User user) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		try{
			conn= DBConnection.getConnection();
			prep= conn.prepareStatement(UPDATE);
			prep.setString(1, user.getEmail());
			prep.setString(2, user.getNickname());
			prep.setString(3, user.getPassword());
			prep.setInt(4, user.getUserIntegral());
			if(user.isEmailVerify()) {
				prep.setString(5, "T");
			} else{
				prep.setString(5, "F");
			  }prep.setString(6, user.getEmailVerifyCode());
			  prep.setLong(7, user.getLastLoginTime());
			  prep.setString(8, user.getLastLoginIp());
			  prep.setInt(9, user.getId());
			  prep.executeUpdate();
			  } finally{
				  DBConnection.close(null, prep, conn);
		}	  
	}
}